package com.anjufang.utils.excel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 表格导入工具,从第二行开始读取
 * @author weichengyu
 *
 */
public class ExcelImport {

	public List<Object[]> importExcel(HttpServletRequest request){
		
		List<Object[]>  dataList = new ArrayList<Object[]>();
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;    
        MultipartFile file = multipartRequest.getFile("file");  
        if(file.isEmpty()){  
            return null;
        }  
		try{			
			DecimalFormat df = new DecimalFormat("#");
			InputStream is = file.getInputStream();
            //创建对Excel工作薄文件的引用
            HSSFWorkbook wookbook=new HSSFWorkbook(is);
            //建立新的sheet对象
            HSSFSheet sheet = wookbook.getSheetAt(0);//wookbook.getSheet("sheet1");
            // 获取Excel的所有行
            int rows = sheet.getPhysicalNumberOfRows();
            // 遍历行
            for (int i = 1; i < rows; i++) {           	
                // 读取左上角单元格
                HSSFRow row = sheet.getRow(i);
                HSSFRow firstRow = sheet.getRow(0);
                // 行不能为空
                if (row != null) {
                    // 获取Excel文件中的所以列
                    int cells = firstRow.getPhysicalNumberOfCells();
                    Object[] obj = new Object[cells];
                    // 遍历列
                    for (int j = 0; j < cells; j++) {
                        // 获取列的值
                        HSSFCell cell = row.getCell(j);
                        if(cell == null) {
                        	obj[j] = "";
                        	continue;
                        }
                        switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_BLANK:
                        	obj[j] = "";
                        	break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                        	obj[j] = df.format(cell.getNumericCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_STRING:
                        	obj[j] = cell.toString();
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                        	obj[j] = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:
                        	obj[j] = String.valueOf(cell.getCellFormula());
                            break;                       
                        default:
                        	obj[j] = cell.toString();
                            break;
                        }                       
                    }
                    dataList.add(obj);
                }               
            }
            if (is != null) {
				is.close();
			}
            return dataList;            
        }catch(FileNotFoundException e){
            e.printStackTrace();
        } catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
	
}
